package org.jivesoftware.openfire.dynamic;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.jivesoftware.database.DbConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class DbDynamicManager {

	private DbDynamicManager() {
    }
	public static DbDynamicManager getInstance() {
        return DynamicManager;		
	}
	private static final Logger log = LoggerFactory.getLogger(DbDynamicManager.class);
	private static final DbDynamicManager DynamicManager = new DbDynamicManager();
	private static final String INSER_QUESTION="insert into ofquestion(publisher,publishTime,questionContent) values(?,?,?)";
	private static final String QUERY_QUESTION="select QUESTIONID,PUBLISHER,PUBLISHTIME,QUESTIONCONTENT from ofquestion order by questionId desc";
	private static final String INSER_ANSWER="insert into ofanswer(questionId,publisher,publishTime,ANSWERCONTENT) values(?,?,?,?)";
	private static final String QUERY_ANSWER="select ANSWERID,QUESTIONID,PUBLISHER,PUBLISHTIME,ANSWERCONTENT from ofanswer where questionId = ? order by answerId desc";
	private static final String LAST_QUESTIONID="SELECT max(questionid) as maxid from ofquestion";
	public static Connection getConnection(){
		Connection con = null;
		 try {		
			 con = DbConnectionManager.getConnection();
			
		}
		 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.debug("DriverManager.getConnection(url,user,pwd) failed");
		}
		return con;
	}
	
	
	public boolean addQuestion(QuestionBean question){
		  Connection con = getConnection();
	        PreparedStatement pstmt = null;
	        try {
	            pstmt = con.prepareStatement(INSER_QUESTION);
	            int i = 1;
	         
	            pstmt.setString(i++, question.getPublisher());
	            pstmt.setString(i++, question.getPublishTime());
	            pstmt.setString(i++, question.getQuestionContent());
	
	            return pstmt.execute();
	        } catch (SQLException sqle) {
	            log.info("Question add exception: {}", sqle);
	            return false;
	        } finally {
	        		 DbConnectionManager.closeConnection(pstmt, con);
	        }
	}
	public HashMap<String,Object> queryMAXquestionId(){
		Connection con = getConnection();
        PreparedStatement pstmt = null;
        HashMap<String, Object> result = new HashMap<String, Object>();
        try {
            pstmt = con.prepareStatement(LAST_QUESTIONID);
            ResultSet rs = pstmt.executeQuery();
            int maxId = 0;
            while (rs.next()) {  
                maxId = rs.getInt("maxid");

            }
            result.put("maxid",maxId);
            log.info("maxId:"+maxId);
            return result;
        } catch (SQLException sqle) {
            log.info("Question add exception: {}", sqle);
            return result;
        } finally {
        		 DbConnectionManager.closeConnection(pstmt, con);
        }
}
	public List<HashMap<String,Object>> queryQuestion(){
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
		try {
			pstmt = con.prepareStatement(QUERY_QUESTION);
	         ResultSet rs = pstmt.executeQuery();
             ResultSetMetaData rsmd = rs.getMetaData();
             int columnCount = rsmd.getColumnCount();     
             while (rs.next()) {  
                 HashMap<String, Object> map = new HashMap<String, Object>();  
               
                 for (int j = 1; j <= columnCount; ++j) {  
                     String columnVal = rs.getString(j);  
                     if (columnVal == null) {  
                         columnVal = "";  
                     }  
                     map.put(rsmd.getColumnName(j), columnVal);  
                 }  
                log.info(map.toString());
                 result.add(map);  
             }  
             return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.info("Hashmap queryQuestion failed");
			return result;
		}finally{
			 DbConnectionManager.closeConnection(pstmt, con);
		}
		 
	}
	public boolean addAnswer(AnswerBean answer){
		  Connection con = getConnection();
	        PreparedStatement pstmt = null;
	        try {
	            pstmt = con.prepareStatement(INSER_ANSWER);
	            int i = 1;
	            pstmt.setInt(i++, answer.getQuestionId());
	            pstmt.setString(i++, answer.getPublisher());
	            pstmt.setString(i++, answer.getPublishTime());
	            pstmt.setString(i++, answer.getQuestionContent());
	
	            return pstmt.execute();
	        } catch (SQLException sqle) {
	            log.info("answer add exception: {}", sqle);
	            return false;
	        } finally {
	        		 DbConnectionManager.closeConnection(pstmt, con);
	        }
	}
	public List<HashMap<String,Object>> queryAnswerOfTheQuestion(int questionId){
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
		try {
			pstmt = con.prepareStatement(QUERY_ANSWER);
			int i =1;
			pstmt.setInt(i, questionId);
	         ResultSet rs = pstmt.executeQuery();
             ResultSetMetaData rsmd = rs.getMetaData();
             int columnCount = rsmd.getColumnCount();     
             while (rs.next()) {  
                 HashMap<String, Object> map = new HashMap<String, Object>();  
               
                 for (int j = 1; j <= columnCount; ++j) {  
                     String columnVal = rs.getString(j);  
                     if (columnVal == null) {  
                         columnVal = "";  
                     }  
                     map.put(rsmd.getColumnName(j), columnVal);  
                 }  
                log.info(map.toString());
                 result.add(map);  
             }  
             return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.info("Hashmap queryQuestion failed");
			return result;
		}finally{
			 DbConnectionManager.closeConnection(pstmt, con);
		}
		 
	}
	
}
